package com.hsnn.medstgmini.base.std.dao.impl;

import java.io.OutputStream;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.dao.StdProdDrugInfoDao;
import com.hsnn.medstgmini.base.std.model.form.StdProdDrugInfoForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.model.DrugStatus;
import com.hsnn.medstgmini.model.MegerStatus;
import com.hsnn.medstgmini.model.Whether;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;
import com.hsnn.medstgmini.util.PinYin;

/**
 * @category 企业产品库管理
 * @author 蔡春龙
 * @date 2015年8月18日
 */
@Repository
public class StdProdDrugInfoDaoImpl extends BaseHibernateDAO implements StdProdDrugInfoDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;
	
	@Value("#{sysProp['system.gzfjydel']}")
	private String gzfjydel ;

	/**
	 * @category 分页查询企业产品列表
	 * @date 2015年8月18日
	 * @param form
	 * @return
	 */
	public Pagination getStdProdDrugInfoList(Pagination page) {
		Session session = factory.getCurrentSession();
		StringBuffer sbCount = new StringBuffer();
		StringBuffer sbList = new StringBuffer();

		sbCount.append(" SELECT COUNT(*) FROM std_proddruginfo a LEFT JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE 1 = 1 ");
		sbList.append(" SELECT a.prod_id prodId, a.drug_code drugCode, b.qualityspec_name qualityspecName,a.comp_name_spel compNameSpel, b.form_code formCode, b.form_name formName, b.spec_code specCode, b.spec_name specName, a.factor, a.unit_code unitCode, a.unit_name unitName, a.material_code materialCode, a.material_name materialName, a.prod_comp_id compId, a.comp_code compCode, a.comp_name compName, a.status, a.audit_status auditStatus, DATE_FORMAT( a.audit_datetime, '%Y-%m-%d %H:%i:%S' ) auditDatetime, a.audit_remark auditRemark, c.name auditUser FROM (select prod_id from std_proddruginfo a left JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE 1 = 1 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();

		// 药品编码
		String drugCode = form.getDrugCode();
		if (StringUtils.isNotBlank(drugCode)) {
			sbCount.append(" AND a.drug_code = :drugCode ");
			sbList.append(" AND a.drug_code = :drugCode ");
		}

		// 通用名
		String qualityspecName = form.getQualityspecName();
		if (StringUtils.isNotBlank(qualityspecName)) {
			sbCount.append(" AND (b.qualityspec_name LIKE :qualityspecName OR b.qualityspec_name_spel LIKE :qualityspecName) ");
			sbList.append(" AND (b.qualityspec_name LIKE :qualityspecName OR b.qualityspec_name_spel LIKE :qualityspecName) ");
		}

		// 生产企业id
		String prodCompId = form.getProdCompId();
		if (StringUtils.isNotBlank(prodCompId)) {
			sbCount.append(" AND a.prod_comp_id = :prodCompId  ");
			sbList.append(" AND a.prod_comp_id = :prodCompId  ");
		}
		
		// 产品id
		String prodId = form.getProdId();
		if(StringUtils.isNotBlank(prodId)){
			sbCount.append(" and a.prod_id not in (:prodIds) ");
			sbList.append(" and a.prod_id not in (:prodIds) ");
		}
		
		// 生产企业名称
		String prodCompName = form.getProdCompName();
		if (StringUtils.isNotBlank(prodCompName)) {
			sbCount.append(" AND (a.comp_name LIKE :prodCompName  OR a.comp_name_spel LIKE :prodCompName) ");
			sbList.append(" AND (a.comp_name LIKE :prodCompName  OR a.comp_name_spel LIKE :prodCompName) ");
		}
		// 状态
		Integer status = form.getStatus();
		if (status != null) {
			sbCount.append(" AND a.status = :status ");
			sbList.append(" AND a.status = :status ");
		}

		sbList.append("  ORDER BY a.drug_code DESC  limit :start, :count ) tmp left join std_proddruginfo a on tmp.prod_id = a.prod_id LEFT JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN sys_user c ON a.audit_user = c.id order by a.drug_code desc ");

		SQLQuery queryCount = session.createSQLQuery(sbCount.toString());
		SQLQuery queryList = session.createSQLQuery(sbList.toString());

		// 药品编码
		if (StringUtils.isNotBlank(drugCode)) {
			queryCount.setString("drugCode", drugCode.trim());
			queryList.setString("drugCode", drugCode.trim());
		}

		// 通用名
		if (StringUtils.isNotBlank(qualityspecName)) {
			queryCount.setString("qualityspecName", "%" + qualityspecName.trim() + "%");
			queryList.setString("qualityspecName", "%" + qualityspecName.trim() + "%");
		}

		// 生产企业id
		if (StringUtils.isNotBlank(prodCompId)) {
			queryCount.setString("prodCompId", prodCompId);
			queryList.setString("prodCompId", prodCompId);
		}

		//产品id
		if(StringUtils.isNotBlank(prodId)){
			List<String> list = new ArrayList<String>();
			String[] strId = prodId.split(",");
			for(int i=0;i<strId.length;i++){
				list.add(strId[i]);
			}
			queryCount.setParameterList("prodIds", list);
			queryList.setParameterList("prodIds", list);
		}
		
		
		// 生产企业名称
		if (StringUtils.isNotBlank(prodCompName)) {
			queryCount.setString("prodCompName", "%" + prodCompName.trim() + "%");
			queryList.setString("prodCompName", "%" + prodCompName.trim() + "%");
		}
		// 状态
		if (status != null) {
			queryCount.setInteger("status", status);
			queryList.setInteger("status", status);
		}

		queryList.setInteger("start", page.getFirstResult());
		queryList.setInteger("count", page.getMaxResults());

		BigInteger count = (BigInteger) queryCount.uniqueResult();

		queryList.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = queryList.list();

		page.setRecords(count.longValue());
		page.setRows(list);

		return page;
	}

	/**
	 * @category 分页查询企业产品列表
	 * @date 2015年8月18日
	 * @param form
	 * @return
	 */
	public Pagination getMergeProdDrugInfoList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName, a.status FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE a.status != 3 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode.trim());
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND (b.qualityspec_name LIKE :qualityspecName OR b.qualityspec_name_spel LIKE :qualityspecName) ");
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName.trim() + "%");
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND a.comp_name LIKE :prodCompName ");
				params.put("prodCompName", "%" + prodCompName.trim() + "%");
			}
		}

		// 需要排除的记录
		List<String> exceptIdList = form.getExceptIdList();
		if (exceptIdList != null && !exceptIdList.isEmpty()) {
			sb.append(" AND a.prod_id NOT IN (:exceptIdList) ");
			params.put("exceptIdList", exceptIdList);
		}

		sb.append(" ORDER BY a.drug_code ASC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);

	}

	/**
	 * @category 根据id查询企业产品详情
	 * @date 2015年8月18日
	 * @param prodId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getStdProdDrugInfoDetail(String prodId) {
		String sql = " SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, a.approval_code approvalCode, DATE_FORMAT(a.approval_date, '%Y-%m-%d' ) approvalDate, a.bus_name busName, b.qualityspec_name qualityspecName, b.qualityspec_name_eng qualityspecNameEng, b.form, b.form_code formCode, b.form_name formName, b.form_status formStatus, b.spec, b.spec_code specCode, b.spec_name specName, b.spec_status specStatus, a.factor, a.unit, a.unit_code unitCode, a.unit_name unitName, a.unit_status unitStatus, a.material_id materialId, a.material_code materialCode, a.material_name materialName, a.material_status materialStatus, a.prod_comp_id prodCompId, a.comp_code prodCompCode, a.comp_name prodCompName, a.comp_status prodCompStatus, a.status, a.drug_attr drugAttr, c.drug_index drugIndex, c.phar_id pharId, c.phar_code pharCode, c.phar_name pharName, c.phar_status pharStatus, c.pattern_type patternType, c.drug_source drugSource, c.is_lowprice isLowprice, c.is_agriculture isAgriculture, c.is_healthcare isHealthcare, c.drug_std_code drugStdCode, c.drug_stdcd_remark drugStdcdRemark, c.remark, a.audit_status auditStatus, DATE_FORMAT( a.audit_datetime, '%Y-%m-%d %T' ) auditDatetime, a.audit_remark auditRemark, a.audit_user auditUser FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id JOIN std_proddruginfo_detail c ON a.prod_id = c.prod_id WHERE 1 = 1 AND a.prod_id = :prodId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("prodId", prodId);
		return (Map<String, Object>) sqlUtil.getSqlMapUniqueResult(sql, params);
	}
	
	/**
	 * @category 根据药品code查询企业产品详情
	 * @date 2015年8月18日
	 * @param prodCode
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getDrugInfoByCode(String drugCode) {
		Session session = factory.openSession();
		String sql = " SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, a.approval_code approvalCode, DATE_FORMAT(a.approval_date, '%Y-%m-%d' ) approvalDate, a.bus_name busName, b.qualityspec_name qualityspecName, b.qualityspec_name_eng qualityspecNameEng, b.form, b.form_code formCode, b.form_name formName, b.form_status formStatus, b.spec, b.spec_code specCode, b.spec_name specName, b.spec_status specStatus, a.factor, a.unit, a.unit_code unitCode, a.unit_name unitName, a.unit_status unitStatus, a.material_id materialId, a.material_code materialCode, a.material_name materialName, a.material_status materialStatus, a.prod_comp_id prodCompId, a.comp_code prodCompCode, a.comp_name prodCompName, a.comp_status prodCompStatus, a.status, a.drug_attr drugAttr, c.drug_index drugIndex, c.phar_id pharId, c.phar_code pharCode, c.phar_name pharName, c.phar_status pharStatus, c.pattern_type patternType, c.drug_source drugSource, c.is_lowprice isLowprice, c.is_agriculture isAgriculture, c.is_healthcare isHealthcare, c.drug_std_code drugStdCode, c.drug_stdcd_remark drugStdcdRemark, c.remark, a.audit_status auditStatus, DATE_FORMAT( a.audit_datetime, '%Y-%m-%d %T' ) auditDatetime, a.audit_remark auditRemark, a.audit_user auditUser,a.comp_name_spel as compNameSpel,a.comp_status as compStatus FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id JOIN std_proddruginfo_detail c ON a.prod_id = c.prod_id WHERE 1 = 1 AND a.drug_code = :drugCode ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("drugCode", drugCode);
		Query query = session.createSQLQuery(sql.toString()).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.list();
		session.clear();
		session.close();
		if (null != list && list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	/**
	 * @category 变更状态
	 * @date 2015年8月18日
	 * @param list
	 * @param status
	 */
	public int changeStatus(List<StdProdDrugInfoForm> list, Integer status,
			SysUser user) {
		int count = 0;
		if (list != null && !list.isEmpty()) {
			List<String> ids = new LinkedList<String>();
			for (StdProdDrugInfoForm drugInfo : list) {
				ids.add(drugInfo.getProdId());
			}
			if (ids != null && !ids.isEmpty()) {
				String sql = " UPDATE std_proddruginfo SET status = :status, upd_user = :updUser, upd_datetime = SYSDATE() WHERE prod_id IN (:ids) and status <> :status and status <> '3'";
				Session session = factory.getCurrentSession();
				SQLQuery query = session.createSQLQuery(sql);
				query.setInteger("status", status);
				query.setString("updUser", user.getId());
				query.setParameterList("ids", ids);
				count = query.executeUpdate();
				session.flush();

				// 记录日志
				for (String id : ids) {
					writeLog(id);
				}
			}
		}
		return count;
	}

	/**
	 * @category 分页查询企业产品日志列表
	 * @date 2015年8月18日
	 * @param form
	 * @return
	 */
	public Pagination getStdProdDrugInfoLogList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT c.detail,a.log_id logId, a.prod_id prodId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName, a.status, e.name updUser, DATE_FORMAT(a.upd_datetime, '%Y-%m-%d %T') updDatetime FROM std_proddruginfo_log a join std_proddruginfo_detail_log c on c.log_id = a.log_id left JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN sys_user e ON a.upd_user = e.id WHERE a.prod_id = :prodId ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {

			String prodId = form.getProdId();
			params.put("prodId", prodId);

			String beginDate = form.getBeginDate();
			if (StringUtils.isNotBlank(beginDate)) {
				sb.append(" AND DATE_FORMAT(a.upd_datetime, '%Y-%m-%d') >= :beginDate ");
				params.put("beginDate", beginDate);
			}

			String endDate = form.getEndDate();
			if (StringUtils.isNotBlank(endDate)) {
				sb.append(" AND DATE_FORMAT(a.upd_datetime, '%Y-%m-%d') <= :endDate ");
				params.put("endDate", endDate);
			}
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 根据id查询企业产品 日志详情
	 * @date 2015年8月18日
	 * @param prodId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getStdProdDrugInfoLogDetail(String logId) {
		StringBuffer sql = new StringBuffer()
		.append(" SELECT a.prod_id prodId, a.drug_code drugCode, a.approval_code approvalCode, DATE_FORMAT(a.approval_date, '%Y-%m-%d') approvalDate,   ")
		.append(" a.bus_name busName, b.qualityspec_name qualityspecName, b.qualityspec_name_eng qualityspecNameEng, b.form_name formName, ")
		.append(" b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName,    ")
		.append(" a.status, o.dic_name drugAttr, e.drug_index drugIndex, e.phar_id pharId, e.phar_name pharName, p.dic_name patternType,  ")
		.append(" q.dic_name drugSource, case e.is_lowprice when 1 then '是' when 0 then '否' when 9 then '未知' else '' end isLowprice, case e.is_agriculture when 1 then '是' when 0 then '否' when 9 then '未知' else '' end  isAgriculture, case e.is_healthcare when 1 then '是' when 0 then '否' when 9 then '未知' else '' end isHealthcare, e.drug_std_code drugStdCode,  ")
		.append(" e.drug_stdcd_remark drugStdcdRemark, e.remark, DATE_FORMAT(a.upd_datetime, '%Y-%m-%d %T') updDatetime, j.name updUser  ")
	    .append(" FROM std_proddruginfo_log a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id  ")
	    .append(" JOIN std_proddruginfo_detail_log e ON a.log_id = e.log_id JOIN ( SELECT g.upd_datetime, g.prod_id FROM std_proddruginfo_log g WHERE g.log_id = :logId ) h ON a.prod_id = h.prod_id AND a.upd_datetime <= h.upd_datetime  ")
	    .append(" left join dic_sysdic o on o.dic_code = a.drug_attr and o.classify_code = :drugAttr ")
		.append(" left join dic_sysdic p on p.dic_code = e.pattern_type and p.classify_code = :patternType ")
		.append(" left join dic_sysdic q on q.dic_code = e.drug_source and q.classify_code = :drugSource ")
	    .append(" LEFT JOIN sys_user j ON a.upd_user = j.id WHERE 1 = 1 ORDER BY a.upd_datetime DESC ");
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql.toString());
		query.setString("logId", logId);
		query.setString("drugAttr", Constants.DRUG_ATTR);
		query.setString("patternType", Constants.PATTERN_TYPE);
		query.setString("drugSource", Constants.DRUG_SOURCE);
		query.setMaxResults(2);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 记录日志
	 * @date 2015年8月19日
	 */
	private void writeLog(String id) {
		String logId = UUID.randomUUID().toString();
		String sql = " INSERT INTO std_proddruginfo_log SELECT :logId, a.* FROM std_proddruginfo a WHERE a.prod_id = :id ";
		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("logId", logId);
		query.setString("id", id);
		query.executeUpdate();

		sql = " INSERT INTO std_proddruginfo_detail_log SELECT :logId, a.*,:detail FROM std_proddruginfo_detail a WHERE a.prod_id = :id ";
		query = session.createSQLQuery(sql);
		query.setString("logId", logId);
		query.setString("detail", getDetail(id));
		query.setString("id", id);
		query.executeUpdate();

		session.flush();
	}

	/**
	 * @category 校验药品编码是否已经存在
	 * @date 2015年9月25日
	 * @param drugCode
	 * @param exceptIds
	 * @return
	 */
	public boolean checkSameDrugCode(String drugCode, String... exceptIds) {
		String sql = " SELECT count(*) FROM std_proddruginfo a WHERE a.drug_code = :drugCode ";
		if (exceptIds != null && exceptIds.length > 0) {
			sql += " AND a.prod_id NOT IN (:exceptId) ";
		}
		SQLQuery query = factory.getCurrentSession().createSQLQuery(sql);
		query.setString("drugCode", drugCode);
		if (exceptIds != null && exceptIds.length > 0) {
			query.setParameterList("exceptId", exceptIds);
		}
		BigInteger count = (BigInteger) query.uniqueResult();
		return count.longValue() > 0;
	}

	/**
	 * @category 校验药品是否已经存在
	 * @date 2015年9月25日
	 * @param drugCode
	 * @param exceptIds
	 * @return
	 */
	public boolean checkSameDrug(StdProdDrugInfoForm form, String... exceptIds) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT COUNT(*) FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE b.qualityspec_name = :qualityspecName AND b.form_name = :formName AND b.spec_name = :specName AND a.unit_name = :unitName AND a.material_name = :materialName AND a.factor = :factor AND a.prod_comp_id = :compId ");
		if (exceptIds != null && exceptIds.length > 0) {
			sb.append(" AND a.prod_id NOT IN (:exceptId) ");
		}
		SQLQuery query = factory.getCurrentSession().createSQLQuery(sb.toString());
		query.setParameter("qualityspecName", form.getQualityspecName());
		query.setParameter("formName", form.getFormName());
		query.setParameter("specName", form.getSpecName());
		query.setParameter("unitName", form.getUnitName());
		query.setParameter("materialName", form.getMaterialName());
		query.setParameter("compId", form.getProdCompId());
		query.setParameter("factor", form.getFactor());
		if (exceptIds != null && exceptIds.length > 0) {
			query.setParameterList("exceptId", exceptIds);
		}
		BigInteger count = (BigInteger) query.uniqueResult();
		return count.longValue() > 0;
	}

	/**
	 * @category 校验药品是否存在
	 * @author 韩守松
	 * @date   2016年1月14日
	 * @param  @param form
	 * @param  @param exceptIds
	 * @param  @return
	 */
	public String checkDrug(StdProdDrugInfoForm form) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT drug_code FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE b.qualityspec_name = :qualityspecName AND b.form_name = :formName AND b.spec_name = :specName AND a.unit_name = :unitName  AND a.comp_code = :compCode ");
		SQLQuery query = factory.getCurrentSession().createSQLQuery(sb.toString());
		query.setParameter("qualityspecName", form.getQualityspecName());
		query.setParameter("formName", form.getFormName());
		query.setParameter("specName", form.getSpecName());
		query.setParameter("unitName", form.getUnitName());
		query.setParameter("compCode", form.getProdCompCode());
		String drugCode = (String) query.uniqueResult();
		return drugCode;
	}
	
	/**
	 * @category 保存企业产品库
	 * @date 2015年8月19日
	 * @param form
	 * @param user
	 */
	public void saveProdDrugInfo(StdProdDrugInfoForm form, SysUser user) {
		if (form != null) {
			String prodId = form.getProdId();
			if (StringUtils.isNotBlank(prodId)) {
				updateProdDrugInfo(form, user);
			} else {
				insertProdDrugInfo(form, user,"0");
			}
		}
	}

	/**
	 * @category 新增药品信息
	 * @date 2015年9月21日
	 * @param form
	 * @param user
	 */
	private void insertProdDrugInfo(StdProdDrugInfoForm form, SysUser user,String type) {
		if (checkSameDrugCode(form.getDrugCode())) {
			throw new MSException("药品编码已存在，请重新输入！");
		}
		if("0".equals(type)){
			if (checkSameDrug(form)) {
				throw new MSException("药品已存在！");
			}
			
			Session session = factory.getCurrentSession();
			String prodId = UUID.randomUUID().toString();

			// 新增药品
			String sql = "insert into std_proddruginfo ( prod_id, qualityspec_id, bus_name, factor, unit, unit_code, unit_name, unit_status, prod_comp_id, comp_code, comp_name,comp_name_spel,comp_status, material_id, material_code, material_name, material_status, approval_code, approval_date, status, upd_datetime, upd_user,  drug_code, audit_status, drug_attr ) values (:prodId, :qualityspecId, :busName, :factor, :unit, :unitCode, :unitName, 1, :prodCompId, :compCode, :compName,:compNameSpel,1, :materialId, :materialCode, :materialName, 1, :approvalCode, :approvalDate, 1, sysdate(), :updUser, :drugCode, 0, :drugAttr)";
			SQLQuery query = session.createSQLQuery(sql);
			query.setString("prodId", prodId);
			query.setString("qualityspecId", form.getQualityspecId());
			query.setString("busName", form.getBusName());
			query.setInteger("factor", form.getFactor());
			query.setString("unit", form.getUnit());
			query.setString("unitCode", form.getUnitCode());
			query.setString("unitName", form.getUnitName());
			query.setString("prodCompId", form.getProdCompId());
			query.setString("compCode", form.getProdCompCode());
			query.setString("compName", form.getProdCompName());
			query.setParameter("compNameSpel",PinYin.getPinYinHeadChar(form.getProdCompName()));
			query.setString("materialId", form.getMaterialId());
			query.setString("materialCode", form.getMaterialCode());
			query.setString("materialName", form.getMaterialName());
			query.setString("approvalCode", form.getApprovalCode());
			query.setDate("approvalDate", form.getApprovalDate());
			query.setString("updUser", user.getId());
			query.setString("drugCode", form.getDrugCode());
			query.setInteger("drugAttr", form.getDrugAttr());
			query.executeUpdate();

			// 新增药品明细
			sql = "insert into std_proddruginfo_detail ( prod_id, qualityspec_id, drug_index, phar_id, phar_code, phar_name, phar_status, pattern_type, drug_source, is_lowprice, is_agriculture, is_healthcare, drug_std_code, drug_stdcd_remark, remark, upd_datetime, upd_user ) values ( :prodId, :qualityspecId, :drugIndex, :pharId, :pharCode, :pharName, 1, :patternType, :drugSource, :isLowprice, :isAgriculture, :isHealthcare, :drugStdCode, :drugStdcdRemark, :remark, sysdate(), :updUser )";
			query = session.createSQLQuery(sql);
			query.setString("prodId", prodId);
			query.setString("qualityspecId", form.getQualityspecId());
			query.setParameter("drugIndex", form.getDrugIndex());
			query.setString("pharId", form.getPharId());
			query.setString("pharCode", form.getPharCode());
			query.setString("pharName", form.getPharName());
			query.setInteger("patternType", form.getPatternType());
			query.setInteger("drugSource", form.getDrugSource());
			query.setInteger("isLowprice", form.getIsLowprice());
			query.setInteger("isAgriculture", form.getIsAgriculture());
			query.setInteger("isHealthcare", form.getIsHealthcare());
			query.setString("drugStdCode", form.getDrugStdCode());
			query.setString("drugStdcdRemark", form.getDrugStdcdRemark());
			query.setString("remark", form.getRemark());
			query.setString("updUser", user.getId());
			query.executeUpdate();

			session.flush();

			// 记录日志
			writeLog(prodId);
		}
		
		if("1".equals(type)){
//			if (!checkDrug(form)) {
				
				Session session = factory.getCurrentSession();
				String prodId = UUID.randomUUID().toString();

				// 新增药品
				String sql = "insert into std_proddruginfo ( prod_id, qualityspec_id, bus_name, factor, unit, unit_code, unit_name, unit_status, prod_comp_id, comp_code, comp_name,comp_name_spel,comp_status, material_id, material_code, material_name, material_status, approval_code, approval_date, status, upd_datetime, upd_user,  drug_code, audit_status, drug_attr ) values (:prodId, :qualityspecId, :busName, :factor, :unit, :unitCode, :unitName, 1, :prodCompId, :compCode, :compName,:compNameSpel,:prodcompStatus, :materialId, :materialCode, :materialName, 1, :approvalCode, :approvalDate, 1, sysdate(), :updUser, :drugCode, 0, :drugAttr)";
				SQLQuery query = session.createSQLQuery(sql);
				query.setParameter("prodId", prodId);
				query.setParameter("qualityspecId", form.getQualityspecId());
				query.setParameter("busName", form.getBusName());
				query.setParameter("factor", form.getFactor());
				query.setParameter("unit", form.getUnit());
				query.setParameter("unitCode", form.getUnitCode());
				query.setParameter("unitName", form.getUnitName());
				query.setParameter("prodCompId", form.getProdCompId());
				query.setParameter("compCode", form.getProdCompCode());
				query.setParameter("compName", form.getProdCompName());
				query.setParameter("compNameSpel",form.getProdCompspel());
				query.setParameter("prodcompStatus", form.getProdCompStatus());
				query.setParameter("materialId", form.getMaterialId());
				query.setParameter("materialCode", form.getMaterialCode());
				query.setParameter("materialName", form.getMaterialName());
				query.setParameter("approvalCode", form.getApprovalCode());
				query.setParameter("approvalDate", form.getApprovalDate());
				query.setParameter("updUser", user.getId());
				query.setParameter("drugCode", form.getDrugCode());
				query.setParameter("drugAttr", form.getDrugAttr());
				query.executeUpdate();

				// 新增药品明细
				sql = "insert into std_proddruginfo_detail ( prod_id, qualityspec_id, drug_index, phar_id, phar_code, phar_name, phar_status, pattern_type, drug_source, is_lowprice, is_agriculture, is_healthcare, drug_std_code, drug_stdcd_remark, remark, upd_datetime, upd_user ) values ( :prodId, :qualityspecId, :drugIndex, :pharId, :pharCode, :pharName, 1, :patternType, :drugSource, :isLowprice, :isAgriculture, :isHealthcare, :drugStdCode, :drugStdcdRemark, :remark, sysdate(), :updUser )";
				query = session.createSQLQuery(sql);
				query.setParameter("prodId", prodId);
				query.setParameter("qualityspecId", form.getQualityspecId());
				query.setParameter("drugIndex", form.getDrugIndex());
				query.setParameter("pharId", form.getPharId());
				query.setParameter("pharCode", form.getPharCode());
				query.setParameter("pharName", form.getPharName());
				query.setParameter("patternType", form.getPatternType());
				query.setParameter("drugSource", form.getDrugSource());
				query.setParameter("isLowprice", form.getIsLowprice());
				query.setParameter("isAgriculture", form.getIsAgriculture());
				query.setParameter("isHealthcare", form.getIsHealthcare());
				query.setParameter("drugStdCode", form.getDrugStdCode());
				query.setParameter("drugStdcdRemark", form.getDrugStdcdRemark());
				query.setParameter("remark", form.getRemark());
				query.setParameter("updUser", user.getId());
				query.executeUpdate();

				session.flush();

				// 记录日志
				writeLog(prodId);
//			}
		}
	}

	/**
	 * @category 更新药品信息
	 * @date 2015年9月21日
	 * @param form
	 * @param user
	 */
	private void updateProdDrugInfo(StdProdDrugInfoForm form, SysUser user) {
		if (checkSameDrugCode(form.getDrugCode(), form.getProdId())) {
			throw new MSException("药品编码已存在，请重新输入！");
		}
		if (checkSameDrug(form, form.getProdId())) {
			throw new MSException("药品已存在！");
		}

		Session session = factory.getCurrentSession();
		// 更新药品
		String sql = "update std_proddruginfo set qualityspec_id = :qualityspecId, bus_name = :busName, factor = :factor, unit = :unit, unit_code = :unitCode, unit_name = :unitName, unit_status = :unitStatus, prod_comp_id = :prodCompId, comp_code = :compCode, comp_name = :compName, comp_status = :compStatus, material_id = :materialId, material_code = :materialCode, material_name = :materialName, material_status = :materialStatus, approval_code = :approvalCode, approval_date = :approvalDate, upd_datetime = sysdate(), upd_user = :updUser,   drug_attr = :drugAttr, audit_status = 0, audit_datetime = null, audit_remark = null, audit_user = null where prod_id = :prodId";
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("prodId", form.getProdId());
		query.setString("qualityspecId", form.getQualityspecId());
		query.setString("busName", form.getBusName());
		query.setInteger("factor", form.getFactor());
		query.setString("unit", form.getUnit());
		query.setString("unitCode", form.getUnitCode());
		query.setString("unitName", form.getUnitName());
		query.setInteger("unitStatus", form.getUnitStatus());
		query.setString("prodCompId", form.getProdCompId());
		query.setString("compCode", form.getProdCompCode());
		query.setString("compName", form.getProdCompName());
		query.setInteger("compStatus", form.getProdCompStatus());
		query.setString("materialId", form.getMaterialId());
		query.setString("materialCode", form.getMaterialCode());
		query.setString("materialName", form.getMaterialName());
		query.setInteger("materialStatus", form.getMaterialStatus());
		query.setString("approvalCode", form.getApprovalCode());
		query.setDate("approvalDate", form.getApprovalDate());
		query.setString("updUser", user.getId());
		query.setInteger("drugAttr", form.getDrugAttr());
		query.executeUpdate();

		// 更新药品明细
		sql = "update std_proddruginfo_detail set qualityspec_id = :qualityspecId, drug_index = :drugIndex, phar_id = :pharId, phar_code = :pharCode, phar_name = :pharName, phar_status = :pharStatus, pattern_type = :patternType, drug_source = :drugSource, is_lowprice = :isLowprice, is_agriculture = :isAgriculture, is_healthcare = :isHealthcare, drug_std_code = :drugStdCode, drug_stdcd_remark = :drugStdcdRemark, remark = :remark, upd_datetime = sysdate(), upd_user = :updUser where prod_id = :prodId ";
		query = session.createSQLQuery(sql);
		query.setString("prodId", form.getProdId());
		query.setString("qualityspecId", form.getQualityspecId());
		query.setParameter("drugIndex", form.getDrugIndex());
		query.setString("pharId", form.getPharId());
		query.setString("pharCode", form.getPharCode());
		query.setString("pharName", form.getPharName());
		query.setInteger("pharStatus", form.getPharStatus());
		query.setInteger("patternType", form.getPatternType());
		query.setInteger("drugSource", form.getDrugSource());
		query.setInteger("isLowprice", form.getIsLowprice());
		query.setInteger("isAgriculture", form.getIsAgriculture());
		query.setInteger("isHealthcare", form.getIsHealthcare());
		query.setString("drugStdCode", form.getDrugStdCode());
		query.setString("drugStdcdRemark", form.getDrugStdcdRemark());
		query.setString("remark", form.getRemark());
		query.setString("updUser", user.getId());
		query.executeUpdate();

		session.flush();

		// 记录日志
		writeLog(form.getProdId());
	}

	/**
	 * 
	 * @category 分页查询品规列表，用于新增通用名、通用名英文、剂型、规格
	 * @author 言科
	 * @date 2015年8月19日
	 */
	public Pagination getSelectQualityspec(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.qualityspec_id qualityspecId, a.qualityspec_code qualityspecCode, a.qualityspec_name qualityspecName,  ");
		sb.append(" a.qualityspec_name_spel qualityspecNameSpel, a.qualityspec_name_eng qualityspecNameEng, a.form_code formCode,  ");
		sb.append(" a.form_name formName, a.spec_code specCode, a.spec_name specName, a.status status,a.remark,d.dic_name dicName ");
		sb.append(" FROM std_drugqualityspec a  ");
		sb.append(" join dic_sysdic d on a.remark = d.dic_code and d.classify_code = :classifyCode where a.status = 1 ");

		// 查询条件
		StdProdDrugInfoForm drugInfoForm = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classifyCode", Constants.QUALITYSPEC_CLASS);
		if (drugInfoForm != null) {
			// 品规编码
			String qualityspecCode = drugInfoForm.getQualityspecCode();
			if (StringUtils.isNotBlank(qualityspecCode)) {
				sb.append(" AND a.qualityspec_code = :qualityspecCode ");
				params.put("qualityspecCode", qualityspecCode.trim());
			}

			// 通用名
			String qualityspecName = drugInfoForm.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				/*sb.append(" AND a.qualityspec_name LIKE :qualityspecName ");*/
				sb.append(" AND (a.qualityspec_name LIKE :qualityspecName OR a.qualityspec_name_spel LIKE :qualityspecName) ");
				params.put("qualityspecName", "%" + qualityspecName.trim() + "%");
			}
		}
		
		if ("1".equals(gzfjydel)) {
			sb.append(" AND a.TRADETYPE = 2");
		}
		
		sb.append(" ORDER BY a.qualityspec_code ASC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		return page;
	}

	/**
	 * 
	 * @category 分页查询药理分类列表
	 * @author 言科
	 * @date 2015年8月19日
	 */
	public Pagination getSelectPhar(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.phar_id pharId, a.phar_code pharCode, a.phar_name pharName, a.status from dic_phar a where a.status = 1 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 药理编码
			String pharCode = form.getPharCode();
			if (StringUtils.isNotBlank(pharCode)) {
				sb.append(" AND a.phar_code = :pharCode ");
				params.put("pharCode", pharCode.trim());
			}

			// 药理名称
			String pharName = form.getPharName();
			if (StringUtils.isNotBlank(pharName)) {
				sb.append(" AND a.phar_name LIKE :pharName ");
				params.put("pharName", "%" + pharName.trim() + "%");
			}

		}

		sb.append(" ORDER BY a.phar_code ASC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 分页查询生产企业列表
	 * @author 言科
	 * @date 2015年8月19日
	 */
	public Pagination getSelectProdComp(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.comp_id prodCompId, a.comp_code prodCompCode, a.comp_name prodCompName, b.ar_fullname arFullname,a.comp_spel_code compSpelCode  from std_compinfo a join dic_area b on a.area_id = b.area_id  where a.comp_type = 1 AND a.status = 1 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 生产企业编码
			String prodCompCode = form.getProdCompCode();
			if (StringUtils.isNotBlank(prodCompCode)) {
				sb.append(" AND a.comp_code = :prodCompCode ");
				params.put("prodCompCode", prodCompCode.trim());
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND (a.comp_name LIKE :prodCompName OR a.comp_spel_code LIKE :prodCompName) ");
				/*sb.append(" AND a.comp_name LIKE :prodCompName ");*/
				params.put("prodCompName", "%" + prodCompName.trim()+ "%");
			}

			// 地区
			String areaId = form.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				sb.append(" AND b.area_id LIKE :areaId ");
				params.put("areaId", "%" + AreaUtil.escapeAreaId(areaId) + "%");
			}

		}

		sb.append(" ORDER BY a.comp_code ASC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 分页查询材质列表
	 * @author 言科
	 * @date 2015年8月19日
	 */
	public Pagination getSelectMaterial(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.material_id materialId, a.material_code materialCode, a.material_name materialName from dic_drugmaterial a where a.status = 1  ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 材质编码
			String materialCode = form.getMaterialCode();
			if (StringUtils.isNotBlank(materialCode)) {
				sb.append(" AND a.material_code = :materialCode ");
				params.put("materialCode", materialCode.trim());
			}

			// 材质名称
			String materialName = form.getMaterialName();
			if (StringUtils.isNotBlank(materialName)) {
				sb.append(" AND a.material_name LIKE :materialName ");
				params.put("materialName", "%" + materialName.trim() + "%");
			}

		}

		sb.append(" ORDER BY a.material_code ASC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 分页查询包装单位列表
	 * @author 言科
	 * @date 2015年8月19日
	 */
	public Pagination getSelectUnit(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.packunit_id unitId, a.packunit_code unitCode, a.packunit_name unitName, a.status from dic_packunit a where status = 1 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 包装单位编码
			String unitCode = form.getUnitCode();
			if (StringUtils.isNotBlank(unitCode)) {
				sb.append(" AND a.packunit_code = :unitCode ");
				params.put("unitCode", unitCode.trim());
			}

			// 包装单位名称
			String unitName = form.getUnitName();
			if (StringUtils.isNotBlank(unitName)) {
				sb.append(" AND a.packunit_name LIKE :unitName ");
				params.put("unitName", "%" + unitName.trim() + "%");
			}

		}

		sb.append(" ORDER BY a.packunit_code ASC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 保存合并
	 * @date 2015年8月21日
	 * @param mergerProdId
	 * @param mergedProdIdList
	 */
	public void saveMergeDrugInfo(String mergerProdId,
			List<StdProdDrugInfoForm> mergedProdIdList, SysUser user) {
		Session session = factory.getCurrentSession();
		for (StdProdDrugInfoForm form : mergedProdIdList) {
			String mergedProdId = form.getProdId();
			// 不能合并自己
			if (mergerProdId.equals(mergedProdId)) {
				continue;
			}

			// 被合并过的不能再次被合并
			String sql = " SELECT COUNT(*) FROM std_proddrugmergeinfo WHERE slave_prod_id = :mergedProdId ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setString("mergedProdId", mergedProdId);
			int count = ((BigInteger) query.uniqueResult()).intValue();

			if (count == 0) {
				// 新增合并记录
				sql = " INSERT INTO std_proddrugmergeinfo VALUES (UUID(), :mergerProdId, :mergedProdId, SYSDATE(), :updUser) ";
				query = session.createSQLQuery(sql);
				query.setString("mergerProdId", mergerProdId);
				query.setString("mergedProdId", mergedProdId);
				query.setString("updUser", user.getId());
				query.executeUpdate();

				// 修改被合并记录状态为合并
				sql = " UPDATE std_proddruginfo SET status = :status, upd_user = :updUser ,upd_datetime = :updDatetime WHERE prod_id = :prodId ";
				query = session.createSQLQuery(sql);
				query.setInteger("status", DrugStatus.merge.getKey());
				query.setString("prodId", mergedProdId);
				query.setString("updUser", user.getId());
				query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
				query.executeUpdate();
			}

		}
		session.flush();
	}

	/**
	 * @category 查询当前产品被谁合并
	 * @date 2015年8月24日
	 * @param form
	 * @param page
	 * @return
	 */
	public Pagination getMergerProdDrugInfoLog(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName, a.status, DATE_FORMAT( e.upd_datetime, '%Y-%m-%d %T' ) updDatetime, f.name updUser FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id JOIN std_proddrugmergeinfo e ON a.prod_id = e.mater_prod_id LEFT JOIN sys_user f ON e.upd_user = f.id WHERE e.slave_prod_id = :prodId ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		String prodId = form.getProdId();
		params.put("prodId", prodId);

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);

	}

	/**
	 * @category 查询当前产品合并了哪些产品
	 * @date 2015年8月24日
	 * @param form
	 * @param page
	 * @return
	 */
	public Pagination getMergedProdDrugInfoLogList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName,b.qualityspec_name_spel qualityspecNameSpel, b.form_name formName, b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName,a.comp_spel_code compSpelCode, a.status, DATE_FORMAT( e.upd_datetime, '%Y-%m-%d %T' ) updDatetime, f.name updUser FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id JOIN std_proddrugmergeinfo e ON a.prod_id = e.slave_prod_id LEFT JOIN sys_user f ON e.upd_user = f.id WHERE e.mater_prod_id = :prodId ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		String prodId = form.getProdId();
		params.put("prodId", prodId);

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode.trim());
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName OR b.qualityspec_name_spel LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName.trim() + "%");
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND (a.comp_name LIKE :prodCompName OR a.comp_spel_code LIKE :prodCompName) ");
				params.put("prodCompName", "%" + prodCompName.trim() + "%");
			}
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 分页查询待审核的企业产品列表
	 * @date 2015年8月18日
	 * @param form
	 * @return
	 */
	public Pagination getAuditCompDrugList(Pagination page) {
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, a.prod_comp_id prodCompId, a.comp_name prodCompName, a.status FROM std_proddruginfo a LEFT JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id  ");
		if (StringUtils.isNotBlank(form.getProdCompName())) {
			if(PinYin.isPinYin(form.getProdCompName())){
				sb.append(" left join std_compinfo t on t.comp_id = a.prod_comp_id ");
			}
		}
		sb.append(" WHERE a.audit_status = 0  ");

		// 查询条件
		
		Map<String, Object> params = new HashMap<String, Object>();
		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode.trim());
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND (b.qualityspec_name LIKE :qualityspecName OR b.qualityspec_name_spel LIKE :qualityspecName) ");
				params.put("qualityspecName", "%" + qualityspecName.trim() + "%");
			}

			// 企业id
			String prodId = form.getProdId();
			if (StringUtils.isNotBlank(prodId)) {
				sb.append(" AND a.prod_comp_id = :prodId ");
				params.put("prodId", prodId);
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				if(PinYin.isPinYin(prodCompName)){
					sb.append(" AND t.comp_spel_code LIKE :prodCompName ");
				}else{
					sb.append(" AND a.comp_name LIKE :prodCompName ");
				}
				params.put("prodCompName", "%" + prodCompName.trim() + "%");
			}
			// 状态
			Integer status = form.getStatus();
			if (status != null) {
				sb.append(" AND a.status = :status ");
				params.put("status", status);
			}
		}

		sb.append(" ORDER BY a.drug_code ASC ");

		return sqlUtil.getSqlPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 审核企业产品
	 * @date 2015年9月11日
	 * @param form
	 * @param user
	 */
	public void auditCompDrug(StdProdDrugInfoForm form, SysUser user) {
		String sql = "UPDATE std_proddruginfo a SET a.audit_status = :auditStatus, a.audit_datetime = SYSDATE(), a.audit_remark = :auditRemark, a.audit_user = :updUser, a.upd_datetime = SYSDATE(), a.upd_user = :updUser WHERE a.prod_id = :prodId";

		Session session = factory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("prodId", form.getProdId());
		query.setInteger("auditStatus", form.getAuditStatus());
		query.setString("auditRemark", form.getAuditRemark());
		query.setString("updUser", user.getId());
		query.executeUpdate();
		session.flush();

		// 记录日志
		writeLog(form.getProdId());
	}
	
	/**获取日志表字段detail的值
	 * @date 2015年9月11日
	 * @param prodId
	 * @return
	 */
	private String getDetail(String prodId){
		StringBuffer sb = new StringBuffer()
		.append("select a.bus_name,c.qualityspec_name,c.form_name,c.spec_name,c.qualityspec_name_eng,a.factor,a.unit_name,")
		.append("a.comp_name,a.material_name,a.approval_code,DATE_FORMAT( a.approval_date, '%Y-%m-%d'),a.status,a.merge_status,")
		.append("a.drug_code,a.audit_status,d.dic_name drug_attr,b.drug_index,b.phar_name,e.dic_name pattern_type,f.dic_name drug_source,b.is_lowprice,b.is_agriculture,")
		.append("b.is_healthcare,b.drug_std_code,b.drug_stdcd_remark,b.remark ");
		StringBuffer ljoin = new StringBuffer()
		.append(" left join std_drugqualityspec c on c.qualityspec_id = a.qualityspec_id ")
		.append(" left join dic_sysdic d on d.dic_code = a.drug_attr and d.classify_code = :drugAttr ")
		.append(" left join dic_sysdic e on e.dic_code = b.pattern_type and e.classify_code = :patternType ")
		.append(" left join dic_sysdic f on f.dic_code = b.drug_source and f.classify_code = :drugSource ");
		StringBuffer sql = new StringBuffer()
		.append(sb.toString())
		.append(" from std_proddruginfo a   join std_proddruginfo_detail b on b.prod_id = a.prod_id  ")
		.append(ljoin.toString())
		.append(" where a.prod_id = :prodId ")
		.append(" union all ")
		.append(" select t.* from ( ")
		.append(sb.toString())
		.append(" from std_proddruginfo_log a  join std_proddruginfo_detail_log b on b.log_id = a.log_id   ")
		.append(ljoin.toString())
		.append(" where a.prod_id = :prodId order by a.upd_datetime ")
		.append(" desc limit 1) t");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("prodId", prodId);
		params.put("drugAttr", Constants.DRUG_ATTR);
		params.put("patternType", Constants.PATTERN_TYPE);
		params.put("drugSource", Constants.DRUG_SOURCE);
		List<?> list = sqlUtil.getSqlList(sql.toString(), params);
		
		StringBuffer detail = new StringBuffer();
		String[] colunm ={"商品名","通用名","剂型","规格","通用名英文","转换系数","包装单位","生产企业","材质","批准文号","批准日期","状态","合并处理状态","药品编码","审核状态","基本药物属性","基药序号","药理分类","专利类型","药品来源","是否低价药","是否农合","是否医保","药品本位码","药品本位码备注","备注"};
		if(list.size()==1){
			Object[] obj =(Object[]) list.get(0);
			obj[11] = obj[11]==null?"":StringUtils.isBlank(obj[11].toString())?"":DrugStatus.getName(Integer.parseInt(obj[11].toString()));
			obj[12] = obj[12]==null?"":StringUtils.isBlank(obj[12].toString())?"":MegerStatus.getName(Integer.parseInt(obj[12].toString()));
			obj[14] = obj[14]==null?"":"0".equals(obj[14].toString())?"未审核":"1".equals(obj[14].toString())?"审核通过":"2".equals(obj[14].toString())?"审核不通过":"未提交";
			obj[20] = obj[20]==null?"":StringUtils.isBlank(obj[20].toString())?"":Whether.getName(Integer.parseInt(obj[20].toString()));
			obj[21] = obj[21]==null?"":StringUtils.isBlank(obj[21].toString())?"":Whether.getName(Integer.parseInt(obj[21].toString()));
			obj[22] = obj[22]==null?"":StringUtils.isBlank(obj[22].toString())?"":Whether.getName(Integer.parseInt(obj[22].toString()));
			for(int i = 0;i<obj.length;i++){
				if(obj[i] != null && StringUtils.isNotBlank(obj[i].toString())){
					detail.append(";").append(colunm[i]).append(":").append(obj[i]==null?"":obj[i].toString());
				}
			}
			detail.delete(0, 1);
		}else{
			Object[] obj1 =(Object[]) list.get(0);//修改后
			Object[] obj2 =(Object[]) list.get(1);//修改前
			obj1[11] = obj1[11]==null?"":StringUtils.isBlank(obj1[11].toString())?"":DrugStatus.getName(Integer.parseInt(obj1[11].toString()));
			obj2[11] = obj2[11]==null?"":StringUtils.isBlank(obj2[11].toString())?"":DrugStatus.getName(Integer.parseInt(obj2[11].toString()));
			obj1[12] = obj1[12]==null?"":StringUtils.isBlank(obj1[12].toString())?"":MegerStatus.getName(Integer.parseInt(obj1[12].toString()));
			obj2[12] = obj2[12]==null?"":StringUtils.isBlank(obj2[12].toString())?"":MegerStatus.getName(Integer.parseInt(obj2[12].toString()));
			obj1[14] = obj1[14]==null?"":"0".equals(obj1[14].toString())?"未审核":"1".equals(obj1[14].toString())?"审核通过":"2".equals(obj1[14].toString())?"审核不通过":"未提交";
			obj2[14] = obj2[14]==null?"":"0".equals(obj2[14].toString())?"未审核":"1".equals(obj2[14].toString())?"审核通过":"2".equals(obj2[14].toString())?"审核不通过":"未提交";
			obj1[20] = obj1[20]==null?"":StringUtils.isBlank(obj1[20].toString())?"":Whether.getName(Integer.parseInt(obj1[20].toString()));
			obj2[20] = obj2[20]==null?"":StringUtils.isBlank(obj2[20].toString())?"":Whether.getName(Integer.parseInt(obj2[20].toString()));
			obj1[21] = obj1[21]==null?"":StringUtils.isBlank(obj1[21].toString())?"":Whether.getName(Integer.parseInt(obj1[21].toString()));
			obj2[21] = obj2[21]==null?"":StringUtils.isBlank(obj2[21].toString())?"":Whether.getName(Integer.parseInt(obj2[21].toString()));
			obj1[22] = obj1[22]==null?"":StringUtils.isBlank(obj1[22].toString())?"":Whether.getName(Integer.parseInt(obj1[22].toString()));
			obj2[22] = obj2[22]==null?"":StringUtils.isBlank(obj2[22].toString())?"":Whether.getName(Integer.parseInt(obj2[22].toString()));
			for(int i = 0;i<obj1.length;i++){
				if(!(obj1[i]==null?"":obj1[i].toString()).equals(obj2[i]==null?"":obj2[i].toString())){
					detail.append(";").append(colunm[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",").append(obj1[i]==null?"":obj1[i].toString());
				}
				
			}
			detail.delete(0, 1);
		}
		return detail.toString();
	}

	 @Override
		public void exportExcel(Pagination page,OutputStream os){
	        XSSFWorkbook wb = new XSSFWorkbook();  
	        XSSFSheet sheet = wb.createSheet("药品");  
	        XSSFRow row = sheet.createRow((int) 0);  
	        XSSFCellStyle style = wb.createCellStyle();  
	        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); 
	        
	        XSSFCell cell = null;
	        String[] headers = {"药品编码","通用名","剂型","规格","转换系数","包装单位","材质","生产企业","状态","基药序号","药理分类编码","药理分类名称","药理分类状态","专利类型","药品来源","是否低价药","是否农合","是否医保","药品本位码","药品本位码备注","说明"};
	        for(int i = 0;i<headers.length;i++){
	        	 cell = row.createCell(i);
	             cell.setCellValue(headers[i]);  
	             cell.setCellStyle(style);  
	        }
	        
	        List<Object> list = getFormNoPage(page);
	        for (int i = 0; i < list.size(); i++)  
	        {  
	            row = sheet.createRow((int) i + 1);  
	            Object[] obj = (Object[]) list.get(i);  
	            for(int j = 0;j<obj.length;j++){
	            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
	            }
	            
	        }  
	        try {   
	            wb.write(os);  
	            os.close();  
	            page.setSuccess(true);
	        } catch (Exception e){  
	           page.setSuccess(false);
	            e.printStackTrace();  
	        } 
	       
		}

		@SuppressWarnings("unchecked")
		private List<Object> getFormNoPage(Pagination page){
			String drugCode = page.getConditions().get("drugCode");
			String qualityspecName = page.getConditions().get("qualityspecName");
			String formName = page.getConditions().get("formName");
			String specName = page.getConditions().get("specName");
			String factor = page.getConditions().get("factor");
			String unitName = page.getConditions().get("unitName");
			String materialName = page.getConditions().get("materialName");
			String prodCompName = page.getConditions().get("prodCompName");
			String status = page.getConditions().get("status");
			StringBuffer sql = new StringBuffer()
			/*.append(" select  a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form_name formName, b.spec_name specName, a.factor,a.unit_name unitName,a.material_name materialName,  a.comp_name prodCompName, ")
			.append(" case a.status when '1' then '启用' when '2' then '停用' when '3' then '合并' end status ")
			.append(" FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id WHERE 1=1 ");*/
			.append(" SELECT a.drug_code drugCode,b.qualityspec_name qualityspecName,b.form_name formName,b.spec_name specName,a.factor,a.unit_name unitName,a.material_name materialName,a.comp_name prodCompName, ")
			.append(" case a.status when '1' then '启用' when '2' then '停用' when '3' then '合并' end status , ")
			.append(" c.drug_index drugIndex, c.phar_code pharCode,c.phar_name pharName, ")
			.append(" case c.phar_status when '1' then '启用' when '0' then '停用' end pharStatus, ") 
			.append(" case c.pattern_type when '1' then '发明专利' when '2' then '实用新型专利' when '3' then '外观设计专利' when '4' then '没有专利' when '9' then '不详' end patternType, ")
			.append(" case c.drug_source when '1' then '国产' when '2' then '进口' when '9' then '不详' end drugSource,  ")
			.append(" case c.is_lowprice when 1 then '是' when 0 then '否' when 9 then '未知' else '' end isLowprice, ")
			.append(" case c.is_agriculture when 1 then '是' when 0 then '否' when 9 then '未知' else '' end  isAgriculture, ")
			.append(" CASE c.is_healthcare WHEN 1 THEN '是' WHEN 0 THEN '否' WHEN 9 THEN '未知' ELSE '' END isHealthcare,c.drug_std_code drugStdCode,c.drug_stdcd_remark drugStdcdRemark,c.remark remark ")
			.append(" FROM std_proddruginfo a  ")
			.append(" left JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id ") 
			.append(" left join std_proddruginfo_detail c ON c.prod_id = a.prod_id ")
			.append(" WHERE 1=1 ");
			if (StringUtils.isNotBlank(drugCode)) {
				sql.append(" and a.drug_code = :drugCode ");
			}
			if (StringUtils.isNotBlank(qualityspecName)) {
				sql.append(" and b.qualityspec_name like :qualityspecName ");
			}
			if (StringUtils.isNotBlank(formName)) {
				sql.append(" and b.form_name like :formName ");
			}
			if (StringUtils.isNotBlank(specName)) {
				sql.append(" and b.spec_name like :specName ");
			}
			if (StringUtils.isNotBlank(factor)) {
				sql.append(" and a.factor like :factor ");
			}
			if (StringUtils.isNotBlank(unitName)) {
				sql.append(" and a.unit_name like :unitName ");
			}
			if (StringUtils.isNotBlank(materialName)) {
				sql.append(" and a.material_name like :materialName ");
			}
			if (StringUtils.isNotBlank(prodCompName)) {
				sql.append(" and a.comp_name like :prodCompName ");
			}
			if (StringUtils.isNotBlank(status)) {
				sql.append(" and a.status = :status ");
			}
			sql.append(" ORDER BY a.drug_code ");
			Query query = getSession().createSQLQuery(sql.toString());
			if (StringUtils.isNotBlank(drugCode)) {
				query.setParameter("drugCode",  drugCode );
			}
			if (StringUtils.isNotBlank(qualityspecName)) {
				query.setParameter("qualityspecName", "%" + qualityspecName + "%");
			}
			if (StringUtils.isNotBlank(formName)) {
				query.setParameter("formName", "%" + formName + "%");
			}
			if (StringUtils.isNotBlank(specName)) {
				query.setParameter("specName", "%" + specName + "%" );
			}
			if (StringUtils.isNotBlank(factor)) {
				query.setParameter("factor",factor );
			}
			if (StringUtils.isNotBlank(unitName)) {
				query.setParameter("unitName", "%" + unitName + "%" );
			}
			if (StringUtils.isNotBlank(materialName)) {
				query.setParameter("materialName", "%" + materialName + "%" );
			}
			if (StringUtils.isNotBlank(prodCompName)) {
				query.setParameter("prodCompName", "%" + prodCompName + "%" );
			}
			if (StringUtils.isNotBlank(status)) {
				query.setParameter("status", status);
			}
			return query.list();
		}
		
		/**
		 * @category 保存不竞不限药品信息
		 * @author 韩守松
		 * @date   2016年1月13日
		 * @param  @param form
		 * @param  @param user
		 */
		public void saveNoJNoXDrugData(StdProdDrugInfoForm form, SysUser user){
			insertProdDrugInfo(form,user,"1");
		}
}
